package com.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class SemesterDAO {
    
    public List<Semester> getAllSemesters() {
        List<Semester> semesters = new ArrayList<>();
        String sql = "SELECT id, name, start_date, end_date, status FROM semesters ORDER BY start_date DESC";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Semester semester = new Semester(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getDate("start_date").toLocalDate(),
                    rs.getDate("end_date").toLocalDate(),
                    rs.getString("status")
                );
                semesters.add(semester);
            }
        } catch (SQLException e) {
            System.err.println("Error fetching semesters: " + e.getMessage());
        }
        return semesters;
    }

    public boolean addSemester(Semester semester) {
        String sql = "INSERT INTO semesters (name, start_date, end_date, status) VALUES (?, ?, ?, ?)";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, semester.getName());
            pstmt.setDate(2, java.sql.Date.valueOf(semester.getStartDate()));
            pstmt.setDate(3, java.sql.Date.valueOf(semester.getEndDate()));
            pstmt.setString(4, semester.getStatus());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error adding semester: " + e.getMessage());
            return false;
        }
    }

    public boolean updateSemester(Semester semester) {
        String sql = "UPDATE semesters SET name = ?, start_date = ?, end_date = ?, status = ? WHERE id = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, semester.getName());
            pstmt.setDate(2, java.sql.Date.valueOf(semester.getStartDate()));
            pstmt.setDate(3, java.sql.Date.valueOf(semester.getEndDate()));
            pstmt.setString(4, semester.getStatus());
            pstmt.setInt(5, semester.getId());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error updating semester: " + e.getMessage());
            return false;
        }
    }

    public boolean deleteSemester(int semesterId) {
        String sql = "DELETE FROM semesters WHERE id = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, semesterId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error deleting semester: " + e.getMessage());
            return false;
        }
    }

    public List<Semester> searchSemesters(String keyword) {
        List<Semester> semesters = new ArrayList<>();
        String sql = "SELECT id, name, start_date, end_date, status FROM semesters WHERE name LIKE ? OR status LIKE ? ORDER BY start_date DESC";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            String searchPattern = "%" + keyword + "%";
            pstmt.setString(1, searchPattern);
            pstmt.setString(2, searchPattern);

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Semester semester = new Semester(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getDate("start_date").toLocalDate(),
                    rs.getDate("end_date").toLocalDate(),
                    rs.getString("status")
                );
                semesters.add(semester);
            }
        } catch (SQLException e) {
            System.err.println("Error searching semesters: " + e.getMessage());
        }
        return semesters;
    }

    public void updateSemesterStatus() {
        String sql = "UPDATE semesters SET status = CASE " +
                    "WHEN start_date > CURRENT_DATE THEN '未开始' " +
                    "WHEN end_date < CURRENT_DATE THEN '已结束' " +
                    "ELSE '进行中' END";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Error updating semester status: " + e.getMessage());
        }
    }
} 